package com.study.javaOther.utils;


import com.study.javaOther.entity.Organization;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 * @ClassName: JdbcUtil
 * @Description:
 * @author: 王胜
 * @date 2021/6/25
 * @Version: 1.0
 **/
public class JdbcUtil {
    static Vector<Connection> pools = new Vector<Connection>();
    static {
        int i = 0;
        while(i<10){
            pools.add(getConn());
            i++;
        }
    }
    public static Connection getConn(){
        Connection conn=null;
        PropertiesUtil.loadFile("jdbc.properties");
        String driver = PropertiesUtil.getPropertyValue("driver");
        String url = PropertiesUtil.getPropertyValue("url");
        String username = PropertiesUtil.getPropertyValue("username");
        String password = PropertiesUtil.getPropertyValue("password");

        try{
            Class.forName(driver);
            conn = DriverManager.getConnection(url,username,password);
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
    public static synchronized Connection getPool(){
        if(pools != null && pools.size() > 0){
            int last_ind = pools.size() -1;
            return pools.remove(last_ind);
        }else{
            return getConn();
        }
    }
    public static int update(String sql,Object[] params) {
        Connection conn = getPool();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                pools.add(conn);
            }
        }
        return 0;
    }
    public static List<Organization> selectByPage(String sql, int page, int pageSizes) {
        Connection conn = getPool();
        List<Organization> organizations=new ArrayList<>();
        PreparedStatement pstmt = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            int start = pageSizes*page;
            int end = pageSizes;
            ps.setInt(1, start);
            ps.setInt(2, end);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Organization organization = new Organization();
                organization.setId(rs.getInt("id"));
                organization.setName(rs.getString("name"));
                organization.setNamePinyin(rs.getString("name_pinyin"));
                organizations.add(organization);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                pools.add(conn);
            }
        }
        return organizations;
    }
    public static int getRowsCount(String tableName){
        String sql = "SELECT COUNT(*) FROM "+tableName;
        int rowCount = 0;
        Connection con =  getPool();
        Statement sta=null;
        ResultSet res=null;
        try {
            sta = con.createStatement();
            res = sta.executeQuery(sql);
            while(res.next()){
                rowCount = res.getInt(1);
            }
        } catch (Exception e) {
            // TODO: handle exception
        }finally {
            try {
                if (con != null) {
                    pools.add(con);
                }
                if (sta != null) {
                    sta.close();
                }
                if (res != null) {
                    res.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return rowCount;
    }
}
